clear
set more off
cd "$station"

*global station "C:\Users\u177172\Dropbox\Labor_cartels"
*cd "$station\oligopsony_ir"

graph set window fontface "Times New Roman"


////////////////////////////////////////////////////////////////////////////////
// Mortality
////////////////////////////////////////////////////////////////////////////////

import excel using ".\data\Leboutte 1991.xlsx", sheet("Table 2") clear firstrow


keep mid Périodes Belgium Namur Liège
drop if mid>1925

twoway (connect Belgium mid, lwidth(medium) msize(medium) mcolor(white) mlcolor(navy) lcolor(navy) msymbol(circle) msize(medium))  ///
	(connect Namur mid, lwidth(medium) msize(medsmall) msymbol(diamond) mcolor(white) mlcolor(maroon) lcolor(maroon) msymbol(diamond) msize(msmall))  ///
	(connect Liège mid, lwidth(medium) msize(medsmall) msymbol(diamond) mcolor(white) mlcolor(green) lcolor(green) msymbol(square) msize(medium)) ///
	, graphregion(color(white)) ytitle("Mortality" "(per 10.0000 workers)") ylabel(,angle(horizontal))  xscale(titlegap(*5)) xtitle("Year") legend(col(3))
graph export "./output/fig/fig_mortality.pdf", replace




////////////////////////////////////////////////////////////////////////////////
// Strikes
////////////////////////////////////////////////////////////////////////////////


import excel using ".\data\Statistiques_Grèves_DATA.xlsx", clear firstrow cellrange(a2)
drop if missing(A)

rename A industry
rename B year
label variable year "Year"
rename Enfaveurdesouvriers n_prolab
rename Enfaveurdespatrons n_proown
rename Partransaction n_neg
rename Sansresultatdéfini n_unc
rename Totaldesgrèves n_tot
rename H emp_prolab
rename I emp_proown
rename J emp_neg
rename K emp_unc
rename L emp_tot

drop M

foreach k of varlist n_prolab n_proown n_neg n_unc n_tot emp_prolab emp_proown emp_neg emp_unc emp_tot {
replace `k' = 0 if missing(`k')
}


gen dt = (industry == "Toutes industries réunies")
drop industry
reshape wide n_* emp_*, i(year) j(dt)


**Number of strikes
gen n_share_tot = (n_tot0/n_tot1)*100
gen n_share_prolab = (n_prolab0/n_prolab1)*100
label variable n_share_tot "Total"
label variable n_share_prolab "Labor demands granted"
scatter n_share_tot n_share_prolab year, graphregion(color(white)) ///
	connect(direct direct) lwidth(medium medium) msymbol(circle diamond) msize(medium msmall) mcolor(white white) mlcolor(navy maroon) lcolor(navy maroon) ///
	yaxis(1) ytitle("Share of strikes (%)", /*orientation(horizontal)*/ axis(1)) ylabel(,angle(horizontal) axis(1)) ///
	xscale(titlegap(*5))
graph export "./output/fig/fig_strikesn.pdf", replace



**Employment of strikes
gen emp_share_tot = (emp_tot0/emp_tot1)*100
gen emp_share_prolab = (emp_prolab0/emp_prolab1)*100
label variable emp_share_tot "Total"
label variable emp_share_prolab "Labor demands granted"
scatter emp_share_tot emp_share_prolab year, graphregion(color(white)) ///
	connect(direct direct) lwidth(medium medium) msymbol(circle diamond) msize(medium msmall) mcolor(white white) mlcolor(navy maroon) lcolor(navy maroon) ///
	yaxis(1) ytitle("Share of striking employment (%)", /*orientation(horizontal)*/ axis(1)) ylabel(,angle(horizontal) axis(1)) ///
	xscale(titlegap(*5))
graph export "./output/fig/fig_strikesemp.pdf", replace




////////////////////////////////////////////////////////////////////////////////
// Petroleum
////////////////////////////////////////////////////////////////////////////////


import excel .\data\revuedutravail_petroleum.xlsx, clear firstrow

collapse (mean) petr (sum) obs, by(community date year)
encode community, gen(id)
xtset id date
tab community
keep if community == "Antwerp" | community == "Bruges" | community == "Brussels" | community == "Charleroi" | community == "Ghent" | community == "Mons" | community == "Liège" | community == "Namur" | community == "Verviers"

save ./data/temp/petroleum.dta, replace

gen period =  (year < 1900)
replace period = 2 if (year > 1909)
drop if period == 0
keep petr community period date
reshape wide petr, i(community date) j(period)

label variable petr1 "1896-1899"
label variable petr2 "1900-1913"
graph bar petr1-petr2, over(community, label(labsize(vsmall) angle(45))) ytitle("Price""(in BEF/liter)") graphregion(color(white)) ///
	legend(label(1 "1896-1899") label(2 "1900-1913")) ylabel(,angle(horizontal)) ///
	bar(1, lcolor(navy) fcolor(navy)) bar(2, lcolor(navy) fcolor(navy*0.3))
graph export "./output/fig/fig_inputprice.pdf", replace




////////////////////////////////////////////////////////////////////////////////
// Wages and employment
////////////////////////////////////////////////////////////////////////////////

import excel ".\data\indices.xlsx", clear firstrow


** Organize variables **********************************************************

drop if year > 1913

qui sum scholliers_mining if year==1910
local base = r(mean)
qui sum gadmin_avgwage if year==1910
local base2 = r(mean)

gen nomwage_mine = (scholliers_mining/`base')*100	//Use data from Scholliers
gen nomwage_mine2 = (gadmin_avgwage/`base2')*100	//Gadisseur data nearly identical


//Wages
gen realwage_industry = segers_nomwage_industry *(cpi_segers/100)
gen realwage_mine = nomwage_mine *(cpi_segers/100)
label variable realwage_industry "Manufacturing (inc. coal mining)"
label variable realwage_mine "Coal mining"

//Capital
label variable gadmin_machinesN "Number of machines"
label variable gadmin_machinesHP "Total horsepower"

//Production
gen productivity = (gadmin_production/gadmin_workersN)*1000
label variable gadmin_production "Production"
label variable productivity "Productivity (per worker)"

//Employment
gen share_male_sec = (buyst_male_mining/buyst_male_secondary)*100
gen share_male_tot = (buyst_male_mining/buyst_male_total)*100
label variable share_male_sec "Male workers - Manufacturing employment"
label variable share_male_tot "Male workers - Total employment"
gen share_total_sec = (buyst_total_mining/buyst_total_secondary)*100
gen share_total_tot = (buyst_total_mining/buyst_total_total)*100
label variable share_total_sec "All workers - Manufacturing employment"
label variable share_total_tot "All workers - Total employment"

gen share2_total_sec = (gadmin_workersN/buyst_total_secondary)*100
gen share2_total_tot = (gadmin_workersN/buyst_total_total)*100
label variable share2_total_sec "Share of manufacturing employment"
label variable share2_total_tot "Share of total employment"


//Prices
gen realprice = wibail_price *(cpi_segers/100)
gen realimportprice = (degreve_imp_pq/degreve_imp_q)*(cpi_segers/100)*1000
gen realexportprice = (degreve_exp_pq/degreve_exp_q)*(cpi_segers/100)*1000
label variable realprice "Domestic price"
label variable realexportprice "Export price"

//Plot data 
////////////////////////////////////////////////////////////////////////////////


/*
//Prices 
preserve
drop if missing(realprice)
line realprice realexportprice year ///
	, lpattern(dash solid) lcolor(gs13 gs3) ///
	graphregion(color(white)) xtitle("Year") ylabel(,angle(horizontal)) ytitle("1910 BEF", /*orientation(horizontal)*/) legend(symxsize(*0.5))
graph export graph_context_prices.pdf, replace
restore
*/

//Wages 
preserve
drop if missing(realwage_industry)
scatter realwage_industry realwage_mine year, connect(direct direct) ///
	lwidth(medium medium) msymbol(circle diamond) msize(medium msmall) mcolor(white white) mlcolor(navy maroon) lcolor(navy maroon)   ///
	graphregion(color(white)) xtitle("Year") ylabel(,angle(horizontal)) ytitle("Base year = 1910", /*orientation(horizontal)*/) xscale(titlegap(*5))
graph export "./output/fig/fig_wind.pdf", replace
restore

/*
//Capital
twoway (scatter gadmin_machinesN year, connect(direct) msymbol(o) msize(small) lcolor(gs3) mcolor(gs3) yaxis(1) ytitle("Number", /*orientation(horizontal)*/ axis(1)) ylabel(,angle(horizontal) axis(1))) /// 
	(scatter gadmin_machinesHP year, connect(direct) msymbol(t) msize(small) lcolor(gs13) mcolor(gs13) yaxis(2) ytitle("HP" "(in 1000s)", /*orientation(horizontal)*/ axis(2)) ylabel(,angle(horizontal) axis(2))), ///
	graphregion(color(white)) xtitle("Year") legend(order(1 2) symxsize(*0.5)) /*xline(1846 1896, lstyle(grid) lpattern(dash) lcolor(gray*2))*/
graph export graph_context_capital.pdf, replace


//Production
twoway (scatter gadmin_production year, connect(direct) msymbol(o) msize(small) lcolor(gs3) mcolor(gs3) yaxis(1) ytitle("Production" "(in 1000 tons)", /*orientation(horizontal)*/ axis(1)) ylabel(,angle(horizontal) axis(1))) /// 
	(scatter productivity year, connect(direct) msymbol(t) msize(small) lcolor(gs13) mcolor(gs13) yaxis(2) ytitle("Productivity" "(ton per worker)", /*orientation(horizontal)*/ axis(2)) ylabel(,angle(horizontal) axis(2))), ///
	graphregion(color(white)) xtitle("Year") legend(order(1 2) symxsize(*0.5)) /*xline(1846 1896, lstyle(grid) lpattern(dash) lcolor(gray*2))*/
graph export graph_context_production.pdf, replace
*/

//Employment
drop if year<1840
scatter share_male_sec share_male_tot share_total_sec share_total_tot year, connect(direct direct direct direct) lpattern(dash solid dash solid) msymbol(t o t o) msize(small small small small) lcolor(gs3 gs3 gs13 gs13) mcolor(gs3 gs3 gs13 gs13) ///
	graphregion(color(white)) xtitle("Year") ylabel(,angle(horizontal)) ytitle("%", /*orientation(horizontal)*/) legend(symxsize(*0.75) cols(1))

drop if year<1840
scatter share2_total_sec share2_total_tot year, connect(direct direct) ///
	lwidth(medium medium) msymbol(circle diamond) msize(medium msmall) mcolor(white white) mlcolor(navy maroon) lcolor(navy maroon)  ///
	graphregion(color(white)) xtitle("Year") ylabel(,angle(horizontal)) ytitle("%", /*orientation(horizontal)*/) /*legend(symxsize(*0.75) cols(1))*/ legend(cols(1)) xscale(titlegap(*5))
graph export "./output/fig/fig_emps.pdf", replace
  

save ./data/temp/indices, replace


////////////////////////////////////////////////////////////////////////////////
// Societe Generale
////////////////////////////////////////////////////////////////////////////////



//	LIST OF SG PARTICIPATION

import excel using "./data/Société Générale.xlsx", clear firstrow //cellrange(a2)
rename YEAR yr

keep if !missing(mine_id) & SHARETYPE == 1 //Identified stocks (no obligations)

sort mine_id yr

//Split mine_id
rename mine_id mine_id1
gen mine_id2 = substr(mine_id1,strpos(mine_id1,";")+1,.) if strpos(mine_id1,";") > 0
replace mine_id1 = substr(mine_id1,1,strpos(mine_id1,";")-1) if strpos(mine_id1,";") > 0

//Reshape to long (to perform split)
order mine_id*
keep mine_id* yr
duplicates drop
preserve
keep if missing(mine_id2)
rename mine_id1 mine_id
tempfile noreshape
save `noreshape'
restore
drop if missing(mine_id2)
reshape long mine_id, i(yr) j(temp)
drop temp
append using `noreshape'
sort mine_id yr

//Reshape to wide for participation identification
egen nr = group(mine_id)
reshape wide mine_id, i(yr) j(nr)
global nr = r(k) - 1
rename mine_id* SG_id*

//Save
tempfile SGid
save `SGid'



//	LOAD INTO DATA


use "./data/temp/data_pf.dta", clear
merge m:1 yr using `SGid', gen(merge_SG)



//Identify participation
gen SG_participation = 0
global varlist "mine_id mine_subid_1 mine_subid_2 mine_subid_3 mine_subid_4 mine_subid_5"
qui {
foreach k of numlist 1/$nr {
foreach var in $varlist {
replace SG_participation = 1 if `var'==SG_id`k' & !missing(SG_id`k')
}
}
}


//Graph
collapse (sum) q, by(SG_participation yr)	

drop if missing(yr)
reshape wide q, i(yr) j(SG_participation)
drop if q0 == 0 //Missing years
replace q1 = 0 if missing(q1)

forval k = 0/1 {
replace q`k' = q`k'/1000
}

twoway (area q0 q1 yr, color(gs10 gs5)) ///
	, graphregion(color(white)) legend(order(1 "No participation" 2 "Participation") col(1)) xtitle("Year") ytitle("Production""(in 1000s Ton)")  ///
	ylabel(,angle(horizontal)) xscale(titlegap(*5))


twoway (area q0 q1 yr, color(navy navy*0.3)) ///
	, graphregion(color(white)) legend(order(1 "No participation" 2 "Participation") col(1)) xtitle("Year") ytitle("Production""(in 1000s Ton)")  ///
	ylabel(,angle(horizontal)) xscale(titlegap(*5))
graph export ./output/fig/fig_SG.pdf, replace

////////////////////////////////////////////////////////////////////////////////
// Commuting and transport infrastructure
////////////////////////////////////////////////////////////////////////////////
 
* commuting data
  cd "$station"
import excel using ./data/commuting-transport/commuting_data.xlsx, firstrow clear
keep resid number distance postal*
rename (number distance postalcode postalcodesieg) (n dist code1 code2)
drop if dist == .	
egen ntot = sum(n)
gen sn = n/ntot 
gen wdist = dist*sn
egen agdist = sum(wdist)
sum agdist	
sum dist
gsort+dist
gen cumn = sum(n)
gen pct = cumn/ntot
label var pct "Cumulative share of workers (%)"
replace pct = pct*100
list pct dist
twoway scatter pct dist, graphregion(color(white)) lwidth(medium) msize(medium) mcolor(white) mlcolor(navy) msymbol(circle) xtitle("Distance (in km)") ylabel(,angle(horizontal)) xscale(titlegap(*5))
graph export ./output/fig/fig_commuters.pdf, replace
 
forvalues n = 1/2 {
tostring code`n', replace
forvalues m = 1/4{
gen code`n'_`m' = substr(code`n',1,`m')
}
}
forvalues m = 1/4 {
gen same_dq`m' = code1_`m'==code2_`m'
gen wsame_dq`m' = same_dq`m'*sn
egen agsame_dq`m' = sum(wsame_dq`m')
}

sum  agsame*	


* Railroad network 
use ./data/temp/data_pf, clear

preserve
drop if emp==. | dtram==. | drail==.
gen emptram = emp*dtram
gen emprail = emp*drail
collapse (sum) emptram emprail emp, by(yr)
gen semprail = emprail/emp
gen semptram = emptram/emp
label var semprail "Railroad"
label var semptram "Tramway"
replace semprail = semprail*100
replace semptram = semptram*100
twoway connect semprail semptram yr , graphregion(color(white)) lwidth(medium medium) msymbol(circle diamond) msize(medium msmall) mcolor(white white) mlcolor(navy maroon) lcolor(navy maroon) xtitle("Year") /// 
 ytitle("Share of connected employment (%)")  ylabel(,angle(horizontal)) xscale(titlegap(*5))
graph export ./output/fig/fig_semprail.pdf, replace
restore
sort yr

label var avdrail "Railroad"
label var avdtram "Tramway"
replace avdrail = avdrail*100
replace avdtram = avdtram*100
twoway connect avdrail avdtram yr , graphregion(color(white))  lwidth(medium medium) msymbol(circle diamond) msize(medium msmall) mcolor(white white) mlcolor(navy maroon) lcolor(navy maroon) xtitle("Year") /// 
 ytitle("Share of connected mines (%)")  ylabel(,angle(horizontal)) xscale(titlegap(*5))
graph export ./output/fig/fig_srail.pdf, replace



////////////////////////////////////////////////////////////////////////////////
// Summary table
////////////////////////////////////////////////////////////////////////////////




use ./data/temp/data_pf, clear 
set matsize 10000
	
drop if missing(yr)

**** Concessions
gen n1 = !missing(q)
gen n2 = 1
bys yr: egen n1t = total(n1)
bys yr: egen n2t = total(n2)


**** Concessions: average 
gen c1 = 1
bys mineid : egen dur = total(c1)

**** Cost shares
egen totalw = rowtotal(wl wm winv)
gen csl = wl/totalw
gen csm = wm/totalw
gen csinv = winv/totalw
gen ccsl = wl/totalw if wl!=0
gen ccsm = wm/totalw if wm!=0
gen ccsinv = winv/totalw if winv!=0


**** Exit shares

xtset mineid yr

gen dx =  emp ~=. & F.emp==. 
replace dx = . if emp==.
replace dx = . if yr==1913	// last year

replace dx =  emp ~=. & F2.emp==.  if yr==1849  
replace dx =  emp ~=. & F5.emp==.  if yr==1885   
replace dx =  emp ~=. & F3.emp==.  if yr==1899   

sum dx 
bys yr: sum dx 
bys yr: egen avdx  = mean(dx )

* weight exit by year
bys yr: egen empyr = sum(emp)
gen sempyr = emp/empyr

gen wdx = dx*sempyr
bys yr: egen agdx  = sum(wdx)
bys yr: egen check = sum(sempyr)




**** Labels
label variable n1t "Total active concessions"
label variable n2t "Total concessions"
label variable dur "Duration of concession in data (years)"
label variable q "Total output (tonnage)"
label variable csl "Cost share: Labor"
label variable csm "Cost share: Materials"
label variable csinv "Cost share: Capital"
label variable ccsl "Cost share: Labor"
label variable ccsm "Cost share: Materials"
label variable ccsinv "Cost share: Capital"
label variable avdx "Exit share of firms"
label variable agdx "Exit share of employment"



**** Write table
estpost summarize ///
    n1t n2t dur ///
	avdx agdx ///
	q /*csl csm csinv*/ ccsl ccsm ccsinv, detail
esttab using ./output/tab/table_summary.tex, cells("min(fmt(2)) p10(fmt(3)) p25(fmt(3)) mean(fmt(3)) p75(fmt(3)) p90(fmt(3)) max(fmt(2))") label nodepvar replace ///
 collabels("Min." "p10" "p25" "Mean" "p75" "p90" "Max.") nolines ///
 booktabs nonum gaps f noobs   ///
prefoot(  \hline    )  posthead( \hline  \\   ) 




////////////////////////////////////////////////////////////////////////////////
// Votes
////////////////////////////////////////////////////////////////////////////////


use ./data/temp/data_pf, clear 
gen cons =1
collapse ssoc* scat* srad* soth* slib*, by(cons)
reshape long ssoc  scat  srad   slib , i(cons) j(yr)
*graph pie slib ssoc srad scat if yr==1895
*graph pie slib ssoc  srad scat  if yr==1899
label var slib "Liberal party"
label var ssoc "Socialist party"
label var scat "Catholic party"
label var srad "Radical party"
 graph bar slib ssoc scat srad , over(yr) blabel(total) legend(label(1 "Liberal party") label(2 "Socialist party") label(3 "Catholic party") label(4 "Radical party") order(1 2 3 4) ) ///
 bar(1, fcolor(navy) /*lwidth(thick) lcolor(black) lpattern(longdash)*/) bar(2, fcolor(maroon) lwidth(thick) lcolor(black) lpattern(longdash)) ///
bar(3, fcolor(green)  lwidth(thick) lcolor(black) lpattern(solid) )  bar(4, fcolor(gold) lwidth(thick) lcolor(black) lpattern(shortdash)) ///
	graphregion(color(white)) ylabel(,angle(horizontal))
graph export ./output/fig/fig_votes.pdf, replace 

exit
